# !/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
===========================
@Time : 2022/10/24 0024 20:44
@Author : 测试开发工程师
@File : 14. 外键和多表.py
@Software: PyCharm
============================
"""

"""

多表及使用场景介绍
多表设计案列
使用多表的优点

创建员工信息表
CREATE TABLE emp(  

emp_id INT PRIMARY KEY AUTO_INCREMENT,  

ename VARCHAR(20),  

age INT,  

gender VARCHAR(10),  

dept_name VARCHAR(20),  

dept_id INT,  

dept_manager VARCHAR(20),  

dept_location VARCHAR(20)  

);  

INSERT INTO emp VALUES (1,'张三', 20, '男','研发部',1,'张无忌','北京');

INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location)

VALUES ('李四', 25, '男','研发部',1,'张无忌','北京');

INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) 

VALUES ('宋江', 40, '男','研发部',1,'张无忌','北京');

INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) 

VALUES ('林冲', 25, '男','研发部',1,'张无忌','北京');

INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) 

VALUES ('林徽因', 25, '女','研发部',1,'张无忌','北京');

INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) 

VALUES ('周芷若', 25, '女','运营部',2,'赵敏','深圳');

INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) 

VALUES ('任盈盈', 25, '女','运营部',2,'赵敏','深圳');

# ==========================================================================================

引入多表：
# 创建员工信息表

CREATE TABLE emp_part(  

emp_id INT PRIMARY KEY AUTO_INCREMENT,  

ename VARCHAR(20),  

age INT ,  

gender VARCHAR(10),

dept_id INT

);



# 创建部门表

CREATE TABLE dept(  

id INT PRIMARY KEY AUTO_INCREMENT,  

dept_name VARCHAR(20),  

dept_manager VARCHAR(20),  

dept_location VARCHAR(20)  

); 

# 向部门表插入数据

INSERT INTO dept(dept_name,dept_manager,dept_location) VALUES('研发部','张无忌','北京');

INSERT INTO dept(dept_name,dept_manager,dept_location) VALUES('运营部','赵敏','深圳');



# 向员工信息表插入数据

INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('李四', 25, '男',1);

INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('宋江', 40, '男',1);

INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('张三', 20, '男',1);

INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('林冲', 25, '男',1);

INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('林徽因', 25,'女',1);

INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('周芷若', 25,'女',2);

INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('任盈盈', 25, '女',2);

# ================   分库分表  ======================
多表的优点：
    简化数据
    提高复用性
    方便权限控制
    提高系统的稳定性和负载能力


主键：可以唯一标识一条记录的列
外键：从表中与主表的主键对应的字段
主表：外键所指向的表,约束其他表的表
从表：外键所在的表，被约束的表
价值：建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性


创建表时添加外键约束：
    CONSTRAINT [外键约束的名称] FOREIGN KEY (外键字段) REFERENCES [主表名称(主键字段)]
添加外键约束：
    ALTER TABLE [表名] ADD CONSTRAINT [外键约束的名称] FOREIGN KEY [外键字段] REFERENCES [主表名称(主键字段)]

# 创建一个关联到主表的从表

CREATE TABLE emp_part(  

emp_id INT PRIMARY KEY AUTO_INCREMENT,  

ename VARCHAR(20),  

age INT ,  

gender VARCHAR(10),

dept_id INT,

# 创建部门表

CREATE TABLE dept(  

id INT PRIMARY KEY AUTO_INCREMENT,  

dept_name VARCHAR(20),  

dept_manager VARCHAR(20),  

dept_location VARCHAR(20)  

); 


-- 添加外键约束 

alter table emp_part add CONSTRAINT emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id)

);

# 插入一条非法数据

INSERT INTO emp_part VALUES(1,'cindy',20,'female','4')



语法：
ALTER TABLE [表名] DROP FOREIGN KEY [外键约束名称]

注意事项
    从表外键数据类型必须与主表的主键一致
    删除数据时，需先删除从表数据再删除主表的数据
    添加数据时先添加主表数据，再添加从表数据
    
# 删除外键约束 
ALTER TABLE emp_part DROP FOREIGN KEY emp_dept 

# 插入一条非法数据
INSERT INTO emp_part VALUES(1,'cindy',20,'female','4') 

SELECT * FROM emp_part  

# 向主表中插入一条数据
INSERT INTO dept VALUES(2,'运营部','张三','北京') 
# 向从表中插入一条数据
INSERT INTO emp_part VALUES(1,'cindy',20,'female','2') 
# 删除主表中的数据 
DELETE FROM dept WHERE id=2

级联删除（逻辑删除）：
    删除主表中数据的同时，删除从表中的数据
    ON DELETE CASCADE


解散运营部


"""
